package homework.day305;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
	
public class EmpStatistics{
	
	static{
	   try{
		  Class.forName("oracle.jdbc.driver.OracleDriver");
	   }catch(ClassNotFoundException e){
		  e.printStackTrace();
	   }
	}
	
	public static void main(String[] args) throws SQLException {
		
		int maxSalary = 10000;
		new EmpStatistics().printStatistics(maxSalary);
		
		maxSalary = 15000;
		new EmpStatistics().printStatistics(maxSalary);
	}
	
	public void printStatistics(int maxSalary) throws SQLException {
		
		System.out.println("==========================");
		System.out.println("Max Salary : " + maxSalary);
		System.out.println("==========================");
		
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		Connection con = DriverManager.getConnection(url, "hr", "hr");
		PreparedStatement pstmt = con.prepareStatement("SELECT"
									+ "\nj.job_title, ROUND(AVG(e.salary), 0)"
									+ "\nFROM employees e, jobs j"
									+ "\nWHERE e.job_id = j.job_id"
									+ "\nAND e.salary >= ?" 
									+ "\nGROUP BY j.job_title"
									+ "\nORDER BY ROUND(AVG(e.salary), 0) DESC");
		pstmt.setInt(1, maxSalary);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()) {
			System.out.println("[" + rs.getString(1) + "] " + rs.getInt(2));
		}
		con.close();
		pstmt.close();
		rs.close();
	}
}